# -*- coding: utf-8 -*-
import pandas as pd
def co2():

    # 读取世界银行气候变化数据集
    df_climate = pd.read_excel("ClimateChange.xlsx", sheetname=0)
    df_country = pd.read_excel("ClimateChange.xlsx", sheetname=1)
    df_climate = df_climate[df_climate["Series code"] == "EN.ATM.CO2E.KT"].replace({"..":None})
    df = df_climate.iloc[:,6:].fillna(method='ffill', axis=1).fillna(method='bfill', axis=1).dropna(how="all").sum(axis=1)
    df_climate['Sum emissions'] = df
    df = pd.merge(df_climate, df_country, how='inner', on=['Country name'])[['Sum emissions', 'Country name', 'Income group']].dropna()
    '''
    补充代码：
    1. 查看数据文件结构。
    2. 将国家和所在的收入群体类别产生关联。
    3. 处理 DataFrame 中的不必要数据和缺失数据。
    3. 尤其是注意这里的缺失值并不是 NaN 的形式。
    4. 将最终返回的 DataFrame 处理成挑战要求的格式 。
    '''
    # 必须返回最终得到的 DataFrame
    df.index = df['Country name']
    results = df.groupby('Income group').sum()
    results['Highest emission country'] = df.groupby(['Income group'])['Sum emissions'].idxmax();
    results['Highest emissions'] = df.groupby(['Income group'])['Sum emissions'].max();
    results['Lowest emission country'] = df.groupby(['Income group'])['Sum emissions'].idxmin();
    results['Lowest emissions'] = df.groupby(['Income group'])['Sum emissions'].min();

    return results


#/home/shiyanlou/anaconda3/bin/python challenge7_1.py
if __name__=='__main__':
    print(co2())

